<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Creating, dropping and altering tables in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="DDL, data definition language, CREATE, DROP, ALTER">
<meta name="description" content="In this part of the SQLIte tutorial, we will be 
creating, dropping and altering tables.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Creating, dropping and altering tables</h1>

<p>
In this part of the SQLite tutorial, we will cover the data definition 
language (DDL) of the SQLite database. The <b>DDL</b> consists 
of SQL statements that define the database schema. The <b>schema</b> 
is the database structure described in a formal language. In relational 
databases, the schema defines the tables, views, indexes, relationships or triggers.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
The SQLite supports the following three DDL statements:
</p>

<ul>
    <li>CREATE</li>
    <li>ALTER TABLE</li>
    <li>DROP</li>
</ul>

<p>
In SQLite, the CREATE statement is used to create tables, indexes, views and 
triggers. The ALTER TABLE statement changes the structure of the table. The DROP 
statement removes tables, indexes, views and triggers. 
</p>


<h2>Creating tables</h2>

<p>
The CREATE statement is used to create tables. It is also used to 
create indexes, views and triggers.
</p>

<p>
To create a table, we give a name to a table and to its columns. 
Each column can have one of these
data types:
</p>

<ul>
    <li>NULL - The value is a NULL value</li>
    <li>INTEGER - a signed integer</li>
    <li>REAL - a floating point value</li>
    <li>TEXT - a text string</li>
    <li>BLOB - a blob of data</li>
</ul>

<pre class="code">
sqlite> CREATE TABLE Testing(Id INTEGER);
sqlite> .schema Testing
CREATE TABLE Testing(Id INTEGER);
</pre>

<p>
We create a simple Testing table with the CREATE TABLE statement. The <b>.schema</b> command
shows the formal definition of the table. 
</p>

<pre class="code">
sqlite> CREATE TABLE Testing(Id INTEGER);
Error: table Testing already exists
</pre>

<p>
If we try to create a table that already exists, we get an error. Therefore
the CREATE TABLE statement has an optional IF NOT EXISTS clause. With this clause
nothing is done and we receive no error.
</p>

<pre class="code">
sqlite> CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);
</pre>

<p>
We get no error message. 
</p>

<p>
The CREATE TABLE ... AS statement enables to create a new table based on a
SELECT statement.
</p>

<pre class="code">
sqlite> CREATE TABLE Cars2 AS SELECT * FROM Cars;
</pre>

<p>
The above statement creates an identical table to Cars table using
a specific SELECT statement.
</p>


<h2>Dropping tables</h2>

<p>
The DROP statement is used to delete a table from a database.
<p>

<pre class="code">
sqlite> .tables
Cars     Friends  Testing
sqlite> DROP TABLE Testing;
sqlite> .tables
Cars     Friends
</pre>

<p>
We show the available tables with the <b>.tables</b> command. The DROP TABLE
statement removes the Testing table from the database. 
</p>

<pre class="code">
sqlite> DROP TABLE Testing;
Error: no such table: Testing
</pre>

<p>
Trying to drop a table that does not exist leads to an error. With the
IF EXISTS clause we can avoid this error.
</p>

<pre class="code">
sqlite> DROP TABLE IF EXISTS Testing;
</pre>

<p>
This statement will drop the Testing table only if it exists. 
</p>


<h2>ALTER TABLE</h2>

<p>
SQLite supports a limited subset of the ALTER TABLE statement. This statement in SQLite allows a 
user to rename a table or to add a new column to an existing table. It is not possible to rename 
a column, remove a column, or add or remove constraints from a table. 
</p>

<pre class="code">
sqlite> CREATE TABLE Names(Id INTEGER, Name TEXT);
</pre>

<p>
Let us have a a table called Names which we want to rename. 
</p>

<pre class="code">
sqlite> ALTER TABLE Names RENAME TO NamesOfFriends;
</pre>

<p>
We rename the table to NamesOfFriends.
</p>

<pre class="code">
sqlite> .schema NamesOfFriends
CREATE TABLE "NamesOfFriends"(Id INTEGER, Name TEXT);
</pre>

<p>
We verify the schema of the renamed table.
</p>

<p>
Say we wanted to add a new column to the table. 
</p>

<pre class="code">
sqlite> ALTER TABLE NamesOfFriends ADD COLUMN Email TEXT;
</pre>

<p>
The SQL statement adds a new column named Email to the table.
</p>

<pre class="code">
sqlite> .schema NamesOfFriends
CREATE TABLE "NamesOfFriends"(Id INTEGER, Name TEXT, Email TEXT);
</pre>

<p>
Here we see the new structure of the table. 
</p>

<p>
In this part of the SQLite tutorial, we were creating, dropping and altering tables. 
</p>


<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 7, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
